﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// 缴费表dao 的摘要说明
/// </summary>
public class 缴费表dao
{
	public 缴费表dao()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    #region  成员方法
		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from 缴费表");
			strSql.Append(" where id=@id ");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
			parameters[0].Value = id;

			 return DataBase.runExecuteScalar(strSql.ToString(), parameters)>=1;
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(缴费表 model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into 缴费表(");
			strSql.Append("personId,insuranceId,code,jfsj,xz,jfgz,jfbl,jsbl,jsfs,jfje,del)");
			strSql.Append(" values (");
			strSql.Append("@personId,@insuranceId,@code,@jfsj,@xz,@jfgz,@jfbl,@jsbl,@jsfs,@jfje,@del)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@personId", SqlDbType.NVarChar,18),
					new SqlParameter("@insuranceId", SqlDbType.NVarChar,50),
					new SqlParameter("@code", SqlDbType.NVarChar,50),
					new SqlParameter("@jfsj", SqlDbType.DateTime),
					new SqlParameter("@xz", SqlDbType.NVarChar,50),
					new SqlParameter("@jfgz", SqlDbType.Float,8),
					new SqlParameter("@jfbl", SqlDbType.Float,8),
					new SqlParameter("@jsbl", SqlDbType.Float,8),
					new SqlParameter("@jsfs", SqlDbType.Int,4),
					new SqlParameter("@jfje", SqlDbType.Float,8),
					new SqlParameter("@del", SqlDbType.Int,4)};
			parameters[0].Value = model.personId;
			parameters[1].Value = model.insuranceId;
			parameters[2].Value = model.code;
			parameters[3].Value = model.jfsj;
			parameters[4].Value = model.xz;
			parameters[5].Value = model.jfgz;
			parameters[6].Value = model.jfbl;
			parameters[7].Value = model.jsbl;
			parameters[8].Value = model.jsfs;
			parameters[9].Value = model.jfje;
			parameters[10].Value = model.del;

			 return DataBase.runExecute(strSql.ToString(), parameters);
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public int Update(缴费表 model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update 缴费表 set ");
			strSql.Append("personId=@personId,");
			strSql.Append("insuranceId=@insuranceId,");
			strSql.Append("code=@code,");
			strSql.Append("jfsj=@jfsj,");
			strSql.Append("xz=@xz,");
			strSql.Append("jfgz=@jfgz,");
			strSql.Append("jfbl=@jfbl,");
			strSql.Append("jsbl=@jsbl,");
			strSql.Append("jsfs=@jsfs,");
			strSql.Append("jfje=@jfje,");
			strSql.Append("del=@del");
			strSql.Append(" where id=@id ");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@personId", SqlDbType.NVarChar,18),
					new SqlParameter("@insuranceId", SqlDbType.NVarChar,50),
					new SqlParameter("@code", SqlDbType.NVarChar,50),
					new SqlParameter("@jfsj", SqlDbType.DateTime),
					new SqlParameter("@xz", SqlDbType.NVarChar,50),
					new SqlParameter("@jfgz", SqlDbType.Float,8),
					new SqlParameter("@jfbl", SqlDbType.Float,8),
					new SqlParameter("@jsbl", SqlDbType.Float,8),
					new SqlParameter("@jsfs", SqlDbType.Int,4),
					new SqlParameter("@jfje", SqlDbType.Float,8),
					new SqlParameter("@del", SqlDbType.Int,4)};
			parameters[0].Value = model.id;
			parameters[1].Value = model.personId;
			parameters[2].Value = model.insuranceId;
			parameters[3].Value = model.code;
			parameters[4].Value = model.jfsj;
			parameters[5].Value = model.xz;
			parameters[6].Value = model.jfgz;
			parameters[7].Value = model.jfbl;
			parameters[8].Value = model.jsbl;
			parameters[9].Value = model.jsfs;
			parameters[10].Value = model.jfje;
			parameters[11].Value = model.del;

            return DataBase.runExecute(strSql.ToString(), parameters);
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public int Delete(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from 缴费表 ");
			strSql.Append(" where id=@id ");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
			parameters[0].Value = id;

            return DataBase.runExecute(strSql.ToString(), parameters);
		}

        /// <summary>
        /// 删除某用户的缴费数据
        /// </summary>
        public int DeleteByPersonId(string personId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from 缴费表 ");
            strSql.Append(" where personId=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id",SqlDbType.NVarChar,18)};
            parameters[0].Value = personId;

            return DataBase.runExecute(strSql.ToString(), parameters);
        }


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public 缴费表 GetModel(int id)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 id,personId,insuranceId,code,jfsj,xz,jfgz,jfbl,jsbl,jsfs,jfje,del from 缴费表 ");
			strSql.Append(" where id=@id ");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
			parameters[0].Value = id;

			缴费表 model=new 缴费表();
            DataSet ds = DataBase.runProcGetSet(strSql.ToString(), parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["id"].ToString()!="")
				{
					model.id=int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
				}
				model.personId=ds.Tables[0].Rows[0]["personId"].ToString();
				model.insuranceId=ds.Tables[0].Rows[0]["insuranceId"].ToString();
				model.code=ds.Tables[0].Rows[0]["code"].ToString();
				if(ds.Tables[0].Rows[0]["jfsj"].ToString()!="")
				{
					model.jfsj=DateTime.Parse(ds.Tables[0].Rows[0]["jfsj"].ToString());
				}
				model.xz=ds.Tables[0].Rows[0]["xz"].ToString();
				if(ds.Tables[0].Rows[0]["jfgz"].ToString()!="")
				{
					model.jfgz=decimal.Parse(ds.Tables[0].Rows[0]["jfgz"].ToString());
				}
				if(ds.Tables[0].Rows[0]["jfbl"].ToString()!="")
				{
					model.jfbl=decimal.Parse(ds.Tables[0].Rows[0]["jfbl"].ToString());
				}
				if(ds.Tables[0].Rows[0]["jsbl"].ToString()!="")
				{
					model.jsbl=decimal.Parse(ds.Tables[0].Rows[0]["jsbl"].ToString());
				}
				if(ds.Tables[0].Rows[0]["jsfs"].ToString()!="")
				{
					model.jsfs=int.Parse(ds.Tables[0].Rows[0]["jsfs"].ToString());
				}
				if(ds.Tables[0].Rows[0]["jfje"].ToString()!="")
				{
					model.jfje=decimal.Parse(ds.Tables[0].Rows[0]["jfje"].ToString());
				}
				if(ds.Tables[0].Rows[0]["del"].ToString()!="")
				{
					model.del=int.Parse(ds.Tables[0].Rows[0]["del"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,personId,insuranceId,code,jfsj,xz,jfgz,jfbl,jsbl,jsfs,jfje,del ");
			strSql.Append(" FROM 缴费表 ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
            return DataBase.runProcGetSet(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" id,personId,insuranceId,code,jfsj,xz,jfgz,jfbl,jsbl,jsfs,jfje,del ");
			strSql.Append(" FROM 缴费表 ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
            return DataBase.runProcGetSet(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "缴费表";
			parameters[1].Value = "ID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/


    public int bulckExecute(缴费表[] objs)
    {
        int count = 0;
        bool ok = true;
        SqlConnection conn = DataBase.getConnection();
        SqlTransaction tran = conn.BeginTransaction();/***** 开始一个事务 *****/
        SqlConnection conn2 = DataBase.getConnection();
        SqlTransaction tran2 = conn2.BeginTransaction();

        try
        {
            for (int i = 0; i < objs.Length; i++)
            {
                缴费表 t = objs[i];
                string sql = "select count(*) FROM 缴费表 where jfsj=@jfsj and xz=@xz and personId=@personId";
                SqlParameter param1 = new SqlParameter("@jfsj", SqlDbType.DateTime);
                SqlParameter param2 = new SqlParameter("@xz", SqlDbType.NVarChar, 50);
                SqlParameter param3 = new SqlParameter("@personId", SqlDbType.NVarChar, 50);
                param1.Value = objs[i].jfsj;
                param2.Value = objs[i].xz;
                param3.Value = objs[i].personId;
                SqlCommand cmd = new SqlCommand(sql, conn2);
                cmd.Transaction = tran2;
                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);
                cmd.Parameters.Add(param3);
                count += Convert.ToInt32(cmd.ExecuteScalar());
            }
            tran2.Commit();
            if (count > 0) ok = false;
        }
        catch (SqlException sqlEx)
        {
            tran2.Rollback();
            //System.Web.HttpContext.Current.Server.ClearError(); // 清除当前的异常信息
            ok = false;
        }
        catch (Exception sysEx)
        {
            tran2.Rollback();
            //System.Web.HttpContext.Current.Server.ClearError();
            ok = false;
        }
        finally
        {
            conn2.Close(); /**** 无论执行结果如何，甚至出现异常，都会关闭数据库连接 ****/
        }

        try
        {
           
            if (ok)
            {
                count = 0;
                for (int i = 0; i < objs.Length; i++)
                {
                    缴费表 model = objs[i];
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("insert into 缴费表(");
                    strSql.Append("personId,insuranceId,code,jfsj,xz,jfgz,jfbl,jsbl,jsfs,jfje,del)");
                    strSql.Append(" values (");
                    strSql.Append("@personId,@insuranceId,@code,@jfsj,@xz,@jfgz,@jfbl,@jsbl,@jsfs,@jfje,@del)");
                    SqlParameter[] parameters = {
					new SqlParameter("@personId", SqlDbType.NVarChar,18),
					new SqlParameter("@insuranceId", SqlDbType.NVarChar,50),
					new SqlParameter("@code", SqlDbType.NVarChar,50),
					new SqlParameter("@jfsj", SqlDbType.DateTime),
					new SqlParameter("@xz", SqlDbType.NVarChar,50),
					new SqlParameter("@jfgz", SqlDbType.Float,8),
					new SqlParameter("@jfbl", SqlDbType.Float,8),
					new SqlParameter("@jsbl", SqlDbType.Float,8),
					new SqlParameter("@jsfs", SqlDbType.Int,4),
					new SqlParameter("@jfje", SqlDbType.Float,8),
					new SqlParameter("@del", SqlDbType.Int,4)};
                    parameters[0].Value = model.personId;
                    parameters[1].Value = model.insuranceId;
                    parameters[2].Value = model.code;
                    parameters[3].Value = model.jfsj;
                    parameters[4].Value = model.xz;
                    parameters[5].Value = model.jfgz;
                    parameters[6].Value = model.jfbl;
                    parameters[7].Value = model.jsbl;
                    parameters[8].Value = model.jsfs;
                    parameters[9].Value = model.jfje;
                    parameters[10].Value = model.del;
                    SqlCommand cmd = new SqlCommand();
                    cmd.Transaction = tran;
                    cmd.Connection = conn;
                    cmd.CommandText = strSql.ToString();
                    if (parameters != null)
                    {
                        foreach (SqlParameter p in parameters)
                        {
                            if (p != null)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                    }

                    count += cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }

        }
        catch (SqlException sqlEx)
        {
            tran.Rollback();
            //System.Web.HttpContext.Current.Server.ClearError(); // 清除当前的异常信息
        }
        catch (Exception sysEx)
        {
            tran.Rollback();
            //System.Web.HttpContext.Current.Server.ClearError();
        }
        finally
        {
            conn.Close(); /**** 无论执行结果如何，甚至出现异常，都会关闭数据库连接 ****/
        }
        if (ok)
            return count;
        else
            return -1;
    }

		#endregion  成员方法
}
